Chapter 4\Employees.sql

CREATE TABLE employees
(
	id		VARCHAR(50)	NOT NULL,
	firstname	VARCHAR(50)	NOT NULL,
	lastname	VARCHAR(50)	NOT NULL,
	street		VARCHAR(50)	NULL,
	city		VARCHAR(50)	NULL,
	state		VARCHAR(50)	NULL,
	zip		VARCHAR(50)	NULL,

	CONSTRAINT pk_employees PRIMARY KEY CLUSTERED ( id )
)
GO

CREATE TABLE phonetypes
(
	phonetype	VARCHAR(50)	NOT NULL,

	CONSTRAINT pk_phonetypes PRIMARY KEY CLUSTERED 
		( phonetype )
)
GO

CREATE TABLE phonenumbers
(
	id		VARCHAR(50)	NOT NULL,
	phonetype	VARCHAR(50)	NOT NULL,
	phonenumber	VARCHAR(50)	NOT NULL,

	CONSTRAINT pk_phonenumbers PRIMARY KEY CLUSTERED 
		( id, phonetype ),

	CONSTRAINT fk_phonenumbers1 FOREIGN KEY ( id )
		REFERENCES employees ( id ),

	CONSTRAINT fk_phonenumbers2 FOREIGN KEY 
		( phonetype ) REFERENCES phonetypes ( phonetype )
)
GO